﻿Imports HMSLibrary
Imports System.Collections.ObjectModel

Public Class BillingDataLayer
    Inherits BillingDataProvider

    Dim SQLQuery As String = String.Empty

    Public Overrides Function PatientBill(ByVal bill As HMSLibrary.Billing, ByVal Mode As FormAction) As Integer
        Try
            Dim objprm(10) As Object
            objprm(0) = bill.BillDate
            objprm(1) = bill.PatientID
            objprm(2) = bill.Remarks
            objprm(3) = bill.SubTotal
            objprm(4) = bill.OtherCharges
            objprm(5) = bill.NetAmt
            objprm(6) = bill.CreatedBy
            objprm(7) = bill.BillCategory
            objprm(8) = bill.AdvReceived
            objprm(9) = bill.IsDischargedBill
            objprm(10) = bill.DischargeDate

            If Mode = FormAction.ADD Then
                Dim objTemp As Object
                objTemp = DataCommon.db.ExecuteScalar("Proc_Insert_Bill", objprm)
                If objTemp Is Nothing Then Return 0
                bill.BillID = objTemp
                MsgBox(CommonMessages.AddMessage, MsgBoxStyle.Information, CommonMessages.MessageTitle)
            ElseIf Mode = FormAction.EDIT Then
                DataCommon.db.ExecuteNonQuery("update_tbl_MyBill", objprm)
                MsgBox(CommonMessages.EditMessage, MsgBoxStyle.Information, CommonMessages.MessageTitle)
            End If
            SaveItemDetails(bill.BillID, Mode, bill.billItems)
            SaveChargesDetails(bill.BillID, Mode, bill.billDisTax)
            SavePaymentDetails(bill.PatientID, bill.BillID, Mode, bill.BillPaymentDetails)
        Catch ex As Exception
            Throw ex
            Return 0
        End Try
        Return bill.BillID
    End Function

    Private Sub SaveItemDetails(ByVal BillID As Integer, ByVal FormMode As FormAction, ByVal billItems As ObservableCollection(Of BillItems))
        Dim objprm(0) As Object
        objprm(0) = BillID
        If FormMode = FormAction.EDIT Then
            If DataCommon.db.ExecuteNonQuery("Proc_Delete_BillItemsDetails", objprm) = 0 Then Exit Sub
        End If
        ReDim Preserve objprm(8)
        Dim i As Integer = 1
        For Each itm In billItems
            If itm.ItemName.Trim = "" Then GoTo nextItem
            objprm(1) = itm.ItemID
            objprm(2) = itm.Qty
            objprm(3) = itm.UOMID
            objprm(4) = itm.Rate
            objprm(5) = itm.Amount
            objprm(6) = itm.Description
            objprm(7) = ""
            objprm(8) = i
            If DataCommon.db.ExecuteNonQuery("Proc_Insert_BillItemsDetails", objprm) = 0 Then Exit Sub
            i += 1
nextItem:
        Next
    End Sub

    Private Sub SaveChargesDetails(ByVal BillID As Integer, ByVal FormMode As FormAction, ByVal DisTax As ObservableCollection(Of BillDiscountTax))
        Dim objprm(0) As Object
        objprm(0) = BillID
        If FormMode = FormAction.EDIT Then
            If DataCommon.db.ExecuteNonQuery("Proc_Delete_BillChargesDetails", objprm) = 0 Then Exit Sub
        End If
        ReDim Preserve objprm(3)
        For Each td In DisTax
            If td.TaxDiscName.Trim = "" Then GoTo nextItem
            objprm(1) = td.TaxDiscID
            objprm(2) = td.Rate
            objprm(3) = td.Amount
            If DataCommon.db.ExecuteNonQuery("Proc_Insert_BillChargesDetails", objprm) = 0 Then Exit Sub
nextItem:
        Next
    End Sub

    Public Overrides Function SavePaymentDetails(PatientId As Long, ByVal BillID As Integer?, ByVal FormMode As HMSLibrary.FormAction, ByVal BillPaymentDetails As HMSLibrary.BillPayDetails) As Integer
        Dim objprm(6) As Object
        objprm(0) = BillID
        objprm(1) = BillPaymentDetails.PayMode
        objprm(2) = BillPaymentDetails.Cheque_CC_No
        objprm(3) = BillPaymentDetails.ChequeDate
        If BillPaymentDetails.BankID = 0 Then objprm(4) = DBNull.Value Else objprm(4) = BillPaymentDetails.BankID
        objprm(5) = BillPaymentDetails.Amount
        objprm(6) = PatientId
        If FormMode = FormAction.ADD Then
            Dim objTemp As Object
            objTemp = DataCommon.db.ExecuteScalar("Proc_Insert_BillPaymentDetails", objprm)
            If objTemp Is Nothing Then Return 0
            Return objTemp
        End If
    End Function

    Public Overrides Function GetPatientBill() As System.Collections.Generic.List(Of HMSLibrary.Billing)
        Dim lst As New List(Of Billing)
        Try
            Dim dr As IDataReader
            SQLQuery = "Exec Proc_GetPatientBill"
            dr = DataCommon.db.ExecuteReader(CommandType.Text, SQLQuery)
            While dr.Read
                Dim obj As New Billing
                obj.BillID = dr.Item("BillID")
                obj.BillDate = dr.Item("BillDate")
                obj.BillCategoryName = DataCommon.DBNullCheck(dr.Item("BillCategory"))
                obj.PatientID = dr.Item("PatientID")
                obj.PatientName = dr.Item("PatientName")
                obj.Remarks = DataCommon.DBNullCheck(dr.Item("Remarks"))
                obj.SubTotal = dr.Item("GrAmt")
                obj.OtherCharges = dr.Item("OtherCharges")
                obj.NetAmt = dr.Item("NetAmt")
                obj.AdvReceived = DataCommon.DBNullCheck(dr.Item("AdvAmt"))
                obj.CreatedByName = dr.Item("UserName")
                obj.CreatedDateTime = dr.Item("CreatedDateTime")
                lst.Add(obj)
            End While
            dr.Close()
        Catch ex As Exception
            Throw ex
        End Try
        Return lst
    End Function

    Public Overrides Function GetPatientBillByID(ByVal BillID As Integer) As HMSLibrary.Billing
        Dim obj As Billing = Nothing
        Try
            Dim dr As IDataReader
            SQLQuery = "Exec Proc_GetPatientBillByID " & BillID
            dr = DataCommon.db.ExecuteReader(CommandType.Text, SQLQuery)
            If dr.Read Then
                obj = New Billing
                obj.BillID = dr.Item("BillID")
                obj.BillDate = dr.Item("BillDate")
                obj.BillCategory = DataCommon.DBNullCheck(dr.Item("BillCategory"))
                obj.PatientID = dr.Item("PatientID")
                obj.Remarks = DataCommon.DBNullCheck(dr.Item("Remarks"))
                obj.SubTotal = dr.Item("GrAmt")
                obj.OtherCharges = dr.Item("OtherCharges")
                obj.NetAmt = dr.Item("NetAmt")
            End If
            dr.Close()
            SQLQuery = "Exec Proc_GetPatientBillByIDPayDetails " & BillID
            dr = DataCommon.db.ExecuteReader(CommandType.Text, SQLQuery)
            If dr.Read Then
                obj.BillPaymentDetails = New BillPayDetails
                obj.BillPaymentDetails.BankID = DataCommon.DBNullCheck(dr.Item("BankID"))
                obj.BillPaymentDetails.PayMode = dr.Item("PayMode")
                obj.BillPaymentDetails.ChequeDate = DataCommon.DBNullCheck(dr.Item("ChequeDate"))
                obj.BillPaymentDetails.Cheque_CC_No = DataCommon.DBNullCheck(dr.Item("Cheque_CC_No"))
                obj.NetAmt = dr.Item("Amount")
            End If
            dr.Close()
            SQLQuery = "Exec Proc_GetPatientBillByIDItemDetails " & BillID
            dr = DataCommon.db.ExecuteReader(CommandType.Text, SQLQuery)
            While dr.Read
                Dim billItemObject As New BillItems
                billItemObject.ItemName = dr.Item("ItemSrv")
                billItemObject.ItemID = dr.Item("ItemID")
                billItemObject.Rate = dr.Item("Rate")
                billItemObject.UOMID = dr.Item("Unit")
                billItemObject.UOMName = dr.Item("UnitName")
                billItemObject.Amount = dr.Item("TotalAmt")
                billItemObject.Description = dr.Item("ItemDesc")
                obj.billItems.Add(billItemObject)
            End While
            dr.Close()

            SQLQuery = "Exec Proc_GetPatientBillByIDChargesDetails " & BillID
            dr = DataCommon.db.ExecuteReader(CommandType.Text, SQLQuery)
            While dr.Read
                Dim billObject As New BillDiscountTax
                billObject.TaxDiscID = dr.Item("Charges")
                billObject.TaxDiscName = dr.Item("Name")
                billObject.Rate = dr.Item("Rate")
                billObject.Amount = dr.Item("TotalAmt")
                obj.billDisTax.Add(billObject)
            End While
            dr.Close()
        Catch ex As Exception
            Throw ex
        End Try
        Return obj
    End Function

    Public Overrides Function GetPatientAdvAmt(PatientId As Long) As Double
        Dim objTemp As Object
        Dim strSql As String = "Proc_GetPatientAdvAmt " & PatientId
        objTemp = DataCommon.db.ExecuteScalar(CommandType.Text, strSql)
        If objTemp Is Nothing Or IsDBNull(objTemp) Then Return 0
        Return objTemp
    End Function
End Class
